package gui;

import gui.dialogs.kedvezmenyPanel;

import java.io.*;
import java.util.Properties;
import java.util.TreeMap;
import java.util.Vector;
import java.sql.*;

import dataTypes.*;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.pool.OracleDataSource;

public class DBConnect {
	
	public OracleDataSource ds;
	public Connection conn;
	public Statement stmt;
	public CallableStatement cstmt = null;
	
	int login(String username, String password) {
		
		connect();
	//	ResultSet rs;
		try {
			cstmt = conn.prepareCall("{? = call azonosit(?,?,?)}");
			//rs = stmt.executeQuery("SELECT count(felhasznalonev) as EREDMENY FROM felhasznalo WHERE felhasznalonev=" + username + " AND jelszo=" + password);
			cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
			cstmt.setString(2, username);
			cstmt.setString(3, password);
			cstmt.setInt(4, 1);
			cstmt.execute();
			int eredmeny = cstmt.getInt(1);
			cstmt.close();
			conn.close();
			/*int eredmeny = 0;
			while(rs.next()) {
				eredmeny = rs.getInt("EREDMENY");
			}
			*/
			return eredmeny;
						
		} catch (SQLException e) {
			System.out.println("SQL hiba login metodusban (SQLException)");
			e.printStackTrace();
			return 0;
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				System.out.println("SQL hiba login/finally metodusban (SQLException)");
				e.printStackTrace();
			} 
		}
	}
	
	public Vector<Naplo> NaploListaz(){
		Vector<Naplo> list = new Vector<Naplo>();
		try {
			connect();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT id, kicsoda, tevekenyseg, to_char(datum,'YYYY.mm.dd. HH24:MI') AS DATUM from naplo");
			while(rs.next()) {
				int id = rs.getInt("id");
				String kicsoda = rs.getString("kicsoda");
				String tev = rs.getString("tevekenyseg");
				String mikor = rs.getString("DATUM");
				list.add(new Naplo(id, mikor, tev, kicsoda));
			}	
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public Vector<Felhasznalo> getFelhasznalok(){
		Vector<Felhasznalo> list = new Vector<Felhasznalo>();
		try {
			connect();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT ID, Felhasznalonev, Keresztnev, Vezeteknev, Email, Jelszo, Jogosultsag, to_char(Utolsobejelentkezes,'YYYY.mm.dd. HH24:MI') AS UTOLSOBEJELENTKEZES from felhasznalo");
			while(rs.next()) {
				int id = rs.getInt("id");
				String felhnev = rs.getString("KERESZTNEV");
				String knev = rs.getString("KERESZTNEV");	
				String vnev = rs.getString("VEZETEKNEV");	
				String email = rs.getString("EMAIL");
				int jogosultsag = rs.getInt("JOGOSULTSAG");
				String utolsobej = rs.getString("UTOLSOBEJELENTKEZES");
				String jelszo = rs.getString("JELSZO");
				
				list.add(new Felhasznalo(id, felhnev, knev, vnev, email, jelszo, utolsobej, jogosultsag));
			}	
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public Vector<Cikk> getCikkek(){
		Vector<Cikk> list = new Vector<Cikk>();
		try {
			connect();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT id, Cikk_cime, FeladoID, Bevezeto, Szoveg, to_char(feladasdatuma,'YYYY.mm.dd. HH24:MI') AS feladasdatuma from cikk");
			while(rs.next()) {
				int id = rs.getInt("id");
				int FeladoID = rs.getInt("FeladoID");
				String Bevezeto = rs.getString("Bevezeto");	
				String Cikk_cime = rs.getString("Cikk_cime");	
				String Feladasdatuma = rs.getString("feladasdatuma");
				String Szoveg = rs.getString("Szoveg");		
				list.add(new Cikk(id, Cikk_cime, FeladoID, Bevezeto, Szoveg, Feladasdatuma, null));
			}	
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public Vector<Terem> getTermek(){
		Vector<Terem> list = new Vector<Terem>();
		try {
			connect();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT teremid, Terem_neve, Teremleiras from terem");
			while(rs.next()) {
				int id = rs.getInt("teremid");
				String Terem_neve = rs.getString("Terem_neve");		
				String Teremleiras = rs.getString("Teremleiras");
				list.add(new Terem(id, Terem_neve, Teremleiras));
			}	
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public TreeMap<Integer,Film> getFilmek(){
		TreeMap<Integer,Film> eredm = new TreeMap<Integer,Film>();
		try {
			connect();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * from film");
			while(rs.next()) {
				eredm.put(new Integer(rs.getInt("filmid")), new Film(rs.getInt("filmid"),rs.getString("cim"),rs.getString("rendezo"), rs.getString("szereplok"), rs.getInt("korhatar"), rs.getInt("mufajid"), rs.getString("leiras"), ((rs.getString("feliratose")).toCharArray()[0]), rs.getString("hangnyelve"), rs.getString("felveteldatuma"),rs.getInt("idotartam"),rs.getString("szarmazasihely"), rs.getString("megjelenesiev")));
				
			}	
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return eredm;
		
	}

	public Vector<Float> getStat(){
		Vector<Float> list = new Vector<Float>();
		try {
			connect();
			stmt = conn.createStatement();
			float napijegy=0, havijegy=0, osszjegy=0, napidb=0, havidb=0, osszdb=0;
			
			ResultSet rs = stmt.executeQuery("SELECT ID, (Kedvezmeny_merteke/100)*(SELECT COUNT(foglalas.id)"
							+ " FROM foglalas WHERE jegytipusid = jegytipus.id) AS arosszes,"
							+ "(Kedvezmeny_merteke/100)*(SELECT COUNT(foglalas.id)"
							+ "	 FROM foglalas WHERE jegytipusid = jegytipus.id AND atvetel_datum BETWEEN trunc(sysdate, 'MM') AND Last_day(trunc(sysdate, 'MM'))) AS arhavi,"
							+ "(Kedvezmeny_merteke/100)*(SELECT COUNT(foglalas.id)"
							+ "	 FROM foglalas WHERE jegytipusid = jegytipus.id AND atvetel_datum BETWEEN sysdate-1 AND sysdate+1) AS arnapi,"
							+ "(SELECT COUNT(foglalas.id)"
							+ "	 FROM foglalas WHERE jegytipusid = jegytipus.id) AS dbosszes, "
							+ "(SELECT COUNT(foglalas.id)"
							+ "	 FROM foglalas WHERE jegytipusid = jegytipus.id AND atvetel_datum BETWEEN trunc(sysdate, 'MM') AND Last_day(trunc(sysdate, 'MM'))) AS dbhavi,"
							+ "(SELECT COUNT(foglalas.id)"
							+ "	 FROM foglalas WHERE jegytipusid = jegytipus.id AND atvetel_datum BETWEEN sysdate-1 AND sysdate+1) AS dbnapi"
							+ " FROM JEGYTIPUS");
			while(rs.next()) {
				napijegy += rs.getFloat("arnapi");
				havijegy += rs.getFloat("arhavi");
				osszjegy += rs.getFloat("arosszes");
				napidb += rs.getFloat("dbnapi");
				havidb += rs.getFloat("dbhavi");
				osszdb += rs.getFloat("dbosszes");
			}	
			list.add(osszjegy);
			list.add(osszdb);
			list.add(havijegy);
			list.add(havidb);
			list.add(napijegy);
			list.add(napidb);
			
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public Vector<Jegytipus> getJegytipusok(){
		Vector<Jegytipus> list = new Vector<Jegytipus>();
		try {
			connect();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT id, jegytipusnev, kedvezmeny_merteke from jegytipus");
			while(rs.next()) {
				int id = rs.getInt("id");
				String jegytipusnev = rs.getString("jegytipusnev");		
				int kedvezmeny_merteke = rs.getInt("kedvezmeny_merteke");
				list.add(new Jegytipus(id, jegytipusnev, kedvezmeny_merteke));
			}	
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public Vector<Integer> getJegytipusForMusor(int musorid){
		Vector<Integer> list = new Vector<Integer>();
		try {
			connect();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT jegytipusid from musorarak where musorid="+musorid);
			while(rs.next()) {
				int id = rs.getInt("jegytipusid");
				
				list.add(new Integer(id));
			}	
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public int UpdateMusorArak(Vector<Integer> kedvezmenyek, int musorid){
		try {
			connect();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("DELETE from musorarak where musorid="+musorid);
			stmt.close();
			for(int i=0;i<kedvezmenyek.size();i++){
				cstmt = conn.prepareCall("begin ? := MUSORARAKADD(?,?); end;");
				cstmt.registerOutParameter(1, OracleTypes.NUMBER);
				cstmt.setInt(2,kedvezmenyek.get(i));
				cstmt.setInt(3,musorid);
				
				cstmt.execute();
				cstmt.close();
			}			
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
		}
		return 0;
	}
	public Vector<Komment> getKommentek(int id, int mode){
		Vector<Komment> list = new Vector<Komment>();
		String sql; //1: filmid 2: id
		if (mode==1) {
			sql="SELECT id, FelhasznaloID, FilmID, Szoveg, to_char(datum,'YYYY.mm.dd. HH24:MI') AS DATUM from komment where filmid = " + id;
		} else {
			sql="SELECT id, FelhasznaloID, FilmID, Szoveg, to_char(datum,'YYYY.mm.dd. HH24:MI') AS DATUM from komment where id = " + id;
		}
		try {
			connect();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()) {
				int kid = rs.getInt("id");
				int FelhasznaloID = rs.getInt("FelhasznaloID");
				int FilmID = rs.getInt("FilmID");
				String Szoveg = rs.getString("Szoveg");		
				String datum = rs.getString("DATUM");
				list.add(new Komment(kid, FelhasznaloID, FilmID, Szoveg, datum));
			}	
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public int setKomment(int id, String komment){
		
		try {
			connect();
			cstmt = conn.prepareCall("begin ? := kommentupdate(?,?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setInt(2, id);
			cstmt.setString(3, komment);			
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}
	}
	
	public String getFelhasznalonev(int id){
		String name = "";
		try {
			connect();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT felhasznalonev from felhasznalo where id = " + id);
			while(rs.next()) {
				name = rs.getString("felhasznalonev");
			}	
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return name;
	}
	
	public Vector<IntStr> Legjobbfilmek(){
		Vector<IntStr> list = new Vector<IntStr>();
		try {
			connect();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT CIM, COUNT(foglalas.id) AS ELADASOK FROM foglalas, film, musor WHERE foglalas.musorid = musor.id AND musor.filmid = film.filmid GROUP BY CIM ORDER BY ELADASOK DESC");
			while(rs.next()) {
				int count = rs.getInt("ELADASOK");
				String mi = rs.getString("CIM");
				list.add(new IntStr(count, mi));
			}	
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public String GetBeallitas(String mit){
		try {
			connect();
			String beallitas = null;
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT Beallitas FROM Beallitasok WHERE Nev='" + mit + "'");
			while(rs.next()) {
				beallitas = rs.getString("BEALLITAS");
			}	
			stmt.close();
			conn.close();
			return beallitas;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public void SetBeallitas(String mit, String mire){
		try {
			connect();
			stmt = conn.createStatement();
			stmt.executeQuery("UPDATE beallitasok SET Beallitas='" + mire + "' WHERE Nev='" + mit + "'");
			stmt.close();
			conn.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public Vector<IntStr> Legjobbeladok(){
		Vector<IntStr> list = new Vector<IntStr>();
		try {
			connect();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT felhasznalonev, COUNT(foglalas.id) AS ELADASOK FROM foglalas, felhasznalo WHERE foglalas.eladoid = felhasznalo.id GROUP BY felhasznalonev ORDER BY ELADASOK DESC");
			while(rs.next()) {
				int count = rs.getInt("ELADASOK");
				String mi = rs.getString("FELHASZNALONEV");
				list.add(new IntStr(count, mi));
			}	
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public Vector<IntStr> getJegyArak(int musorid){
		Vector<IntStr> list = new Vector<IntStr>();
		try {
			connect();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select jegytipusid,(select beallitas from beallitasok where nev = 'Alapar')-((select beallitas from beallitasok where nev = 'Alapar')*kedvezmeny_merteke/100) AS ar from jegytipus inner join musorarak on musorarak.jegytipusid = jegytipus.id where musorid = "+musorid);
			while(rs.next()) {
				int count = rs.getInt("jegytipusid");
				String mi = Integer.toString(rs.getInt("ar"));
				list.add(new IntStr(count, mi));
			}	
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
		
	}
	public Vector<IntStr> KedvezmenyLista(int musor){
		Vector<IntStr> list = new Vector<IntStr>();
		try {
			connect();

			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT id, jegytipusnev from jegytipus inner join musorarak on musorarak.jegytipusid=jegytipus.id where musorarak.musorid = "+musor);
			
			while(rs.next()) {
				int count = rs.getInt("id");
				String mi = rs.getString("jegytipusnev");
				list.add(new IntStr(count, mi));
			}	
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public int getVegosszeg(int[] foglalasid){
		int alapar = Integer.parseInt(GetBeallitas("Alapar"));
		int osszeg=0;
		try {
			connect();

			stmt = conn.createStatement();
			for (int i=0;i<foglalasid.length;i++){
				ResultSet rs = stmt.executeQuery("select kedvezmeny_merteke from jegytipus inner join foglalas on jegytipus.id=foglalas.jegytipusid where foglalas.id ="+foglalasid[i]);
				
				while(rs.next()) {
					int kedv = rs.getInt("kedvezmeny_merteke");
					osszeg+=alapar-(kedv*alapar/100);
				}
			}
	
			stmt.close();
			conn.close();
			return osszeg;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return osszeg;
		}
	}
	public Vector<IntStr> getFilmCimek(){
		connect();
		Vector<IntStr> elemek = new Vector<IntStr>();
		try {
			cstmt = conn.prepareCall("begin ? := FILMCIMEK(); end;");
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			cstmt.execute();
			ResultSet cursor =((OracleCallableStatement)cstmt).getCursor(1);

			while( cursor.next() ){
			elemek.add(new IntStr(cursor.getInt("filmid"), cursor.getString("cim")));
			}
			cstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		return elemek;
	}
	public Vector<IntStr> getMusorIdopontok(int musorID, java.sql.Date datum){
		connect();
		Vector<IntStr> elemek = new Vector<IntStr>();
		try {
			cstmt = conn.prepareCall("begin ? := IDOPONTOK(?,?); end;");
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			cstmt.setInt(2,musorID);
			cstmt.setDate(3, datum);
			cstmt.execute();
			ResultSet cursor =((OracleCallableStatement)cstmt).getCursor(1);

			while( cursor.next() ){
			elemek.add(new IntStr(cursor.getInt("ID"), cursor.getString("ido")));
			}
			
			cstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		return elemek;
	}
	public Vector<Musor> getMusor(int teremid, java.sql.Date datum){
		
		Vector<Musor> eredm = new Vector<Musor>();
		try {
			connect();
			stmt = conn.createStatement();
			
			ResultSet rs = stmt.executeQuery("SELECT * FROM MUSOR WHERE terem="+teremid+" AND datum=to_date('"+datum+"','YYYY-MM-DD')");
			
			while(rs.next()){
				eredm.add(new Musor(rs.getInt("ID"),rs.getInt("filmid"), rs.getDate("datum"),rs.getTimestamp("ido"), rs.getInt("terem")));
			}
			stmt.close();
			conn.close();
			return eredm;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	public int UpdateTerem(dataTypes.Terem t){
		connect();
		try {
			
			cstmt = conn.prepareCall("begin ? := teremupdate(?,?,?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setInt(2, t.Teremid);
			cstmt.setString(3, t.teremnev);
			cstmt.setString(4, t.teremleiras);
			
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}
	}
	
	public int UpdateJegytipus(dataTypes.Jegytipus j){
		connect();
		try {
			
			cstmt = conn.prepareCall("begin ? := jegytipusupdate(?,?,?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setInt(2, j.id);
			cstmt.setString(3, j.jegytipusnev);
			cstmt.setInt(4, j.kedvezmenymertek);
			
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}
	}
	
	public int UpdateCikk(dataTypes.Cikk c){
		connect();
		try {
			
			cstmt = conn.prepareCall("begin ? := cikkupdate(?,?,?,?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setInt(2, c.ID);
			cstmt.setString(3, c.Cikk_cime);
			cstmt.setString(4, c.Bevezeto);
			cstmt.setString(5, c.Szoveg);
			
			
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}
	}
	
	public int UpdateFilm(dataTypes.Film f){
		connect();
		try {
			
			cstmt = conn.prepareCall("begin ? := filmupdate(?,?,?,?,?,?,?,?,?,?,?,?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setInt(2, f.filmid);
			cstmt.setString(3, f.cim);
			cstmt.setString(4, f.rendezo);
			cstmt.setString(5, f.szereplok);
			cstmt.setInt(6, f.korhatar);
			cstmt.setInt(7, f.mufajid);
			cstmt.setString(8, f.leiras);
			 
			cstmt.setString(9,Character.toString(f.feliratose));
			
			
			cstmt.setString(10,f.hangnyelve);
			cstmt.setString(11, f.szarmazasihely);
			cstmt.setInt(12, f.idotartam);
			cstmt.setString(13, f.megjelenesiev);
				
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
		//	System.out.println(eredm);
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}
	}
	public int AddFilm(dataTypes.Film f){
		connect();
		try {
			
			cstmt = conn.prepareCall("begin ? := filmadd(?,?,?,?,?,?,?,?,?,?,?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setString(2, f.cim);
			cstmt.setString(3, f.rendezo);
			cstmt.setString(4, f.szereplok);
			cstmt.setInt(5, f.korhatar);
			cstmt.setInt(6, f.mufajid);
			cstmt.setString(7, f.leiras);
			 
			cstmt.setString(8,Character.toString(f.feliratose));
			
			
			cstmt.setString(9,f.hangnyelve);
			cstmt.setString(10, f.szarmazasihely);
			cstmt.setInt(11, f.idotartam);
			cstmt.setString(12, f.megjelenesiev);
				
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
		//	System.out.println(eredm);
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}
	}
	public int UpdateMusor(dataTypes.Musor m){
		connect();
		try {
			java.sql.Date d = new java.sql.Date(m.datum.getTime());
			cstmt = conn.prepareCall("begin ? := musorupdate(?,?,?,?,?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setInt(2, m.id);
			cstmt.setInt(3, m.filmid);
			cstmt.setDate(4, d);
			cstmt.setTimestamp(5,m.ido);
			cstmt.setInt(6, m.terem);
			
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}
	}
	
	public int UpdateFelhasznalo(dataTypes.Felhasznalo f){
		connect();
		try {
			
			cstmt = conn.prepareCall("begin ? := felhmod(?,?,?,?,?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setInt(2, f.ID);
			cstmt.setString(3, f.knev);
			cstmt.setString(4, f.vnev);
			cstmt.setString(5, f.email);
			cstmt.setString(6, f.jelszo);
			
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
			
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}
	}
	
	public int DeleteMusor(int musorid){
		connect();
		try {
			cstmt = conn.prepareCall("begin ? := musordelete(?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setInt(2, musorid);
			
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}
	}
	
	public int DeleteKomment(int kommentid){
		connect();
		try {
			cstmt = conn.prepareCall("begin ? := kommentdelete(?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setInt(2, kommentid);
			
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}
	}
	
	public int DeleteCikk(int id){
		connect();
		try {
			cstmt = conn.prepareCall("begin ? := cikkdelete(?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setInt(2, id);
			
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}
	}
	
	public int DeleteFelhasznalo(int id){
		connect();
		try {
			cstmt = conn.prepareCall("begin ? := felhdelete(?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setInt(2, id);
			
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}
	}
	
	public int DeleteJegytipus(int id){
		connect();
		try {
			cstmt = conn.prepareCall("begin ? := jegytipusdelete(?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setInt(2, id);
			
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}
	}
	
	public int DeleteTerem(int id){
		connect();
		try {
			cstmt = conn.prepareCall("begin ? := teremdelete(?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setInt(2, id);
			
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}
	}
	
	public int DeleteFilm(int id){
		connect();
		try {
			cstmt = conn.prepareCall("begin ? := filmdelete(?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setInt(2, id);
			
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}
	}
	
	public int AddMusor(dataTypes.Musor m){
		connect();
		try {
			java.sql.Date d = new java.sql.Date(m.datum.getTime());
			cstmt = conn.prepareCall("begin ? := musoradd(?,?,?,?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			
			cstmt.setInt(2, m.filmid);
			cstmt.setDate(3, d);
			cstmt.setTimestamp(4,m.ido);
			cstmt.setInt(5, m.terem);
			
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}
	}
	public Vector<IntStr> getFerohelyek(int musorid, int sor){
		connect();
		Vector<IntStr> elemek = new Vector<IntStr>();
		try {
			cstmt = conn.prepareCall("begin ? := foglalthelyekj(?,?); end;");
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			cstmt.setInt(2, musorid);
			cstmt.setInt(3, sor);
			cstmt.execute();
			ResultSet cursor =((OracleCallableStatement)cstmt).getCursor(1);

			while( cursor.next() ){
			elemek.add(new IntStr(cursor.getInt("szekszam"), cursor.getString("foglalt")));
			}
			cstmt.close();
			conn.close();
			return elemek;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	public Felhasznalo getFelhasznaloAdatok(String felhasznalonev){
		
		Felhasznalo user;
		try {
			connect();

			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM FELHASZNALO WHERE felhasznalonev='"+felhasznalonev+"'");
			
				rs.next();
				user = new Felhasznalo(rs.getInt("id"), rs.getString("felhasznalonev"), rs.getString("keresztnev"), rs.getString("vezeteknev"), rs.getString("email"), rs.getString("jelszo"),rs.getString("utolsobejelentkezes"),rs.getInt("jogosultsag"));
			
			stmt.close();
			conn.close();
			return user;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	public IntStr getTerem(int id){
		connect();
		IntStr terem;
		try {
			cstmt = conn.prepareCall("begin ? := GETTEREM(?); end;");
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			cstmt.setInt(2, id);
			cstmt.execute();
			ResultSet cursor =((OracleCallableStatement)cstmt).getCursor(1);

			cursor.next();
			terem = new IntStr(cursor.getInt("teremid"), cursor.getString("terem_neve"));
			
			cstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			terem = new IntStr(0,"hiba");
		}
		return terem;
	}
	public int getSorszam(int musor){
		connect();
		int sorok;
		try {
			cstmt = conn.prepareCall("begin ? := GETTEREMSORSZAM(?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setInt(2, musor);
			cstmt.execute();
			sorok = cstmt.getInt(1);
			cstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			sorok = -1;
		}
		return sorok;
	}

	public Vector<Integer> getSorSzekszam(int musor){
		connect();
		Vector<Integer> v = new Vector<Integer>();
		try {
			cstmt = conn.prepareCall("begin ? := SZEKSZAMA(?); end;");
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			cstmt.setInt(2, musor);
			cstmt.execute();
			ResultSet cursor =((OracleCallableStatement)cstmt).getCursor(1);

			while( cursor.next() ){
			v.add(new Integer(cursor.getInt("ferohely")));
			}
			cstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		}	
		
		return v;
	}
	public String lefoglal(Foglalas[] fogl){
		connect();
		String vissza = new String("Hiba a kapcsolódásban!");
		
		vissza=new String("Hiba a kapcsolódásban!");
		
		int []eredmenyek = new int[fogl.length];
		
		try{
			for(int i=0;i<fogl.length;i++){
				cstmt = conn.prepareCall("begin ? := foglal(?,?,?,?,?); end;");
				cstmt.registerOutParameter(1, OracleTypes.NUMBER);
				cstmt.setInt(2, fogl[i].jegytipusid);
				cstmt.setInt(3, fogl[i].musorid);
				cstmt.setInt(4, fogl[i].szekszam);
				cstmt.setInt(5, fogl[i].eladoid);
				cstmt.setInt(6, fogl[i].foglaloid);
				cstmt.execute();
				eredmenyek[i]=cstmt.getInt(1);
			}
			return jegyatvetel(eredmenyek);
		}catch (SQLException e){
				e.printStackTrace();
				return vissza;
		}
		
	}
	public String jegyatvetel(int []foglalasid){
		connect();
		String vissza = new String("Hiba a kapcsolódásban!");
		int []eredmenyek = new int[foglalasid.length];
		try {
			for(int i=0;i<foglalasid.length;i++){
				cstmt = conn.prepareCall("begin ? := jegykiadas(?); end;");
				cstmt.registerOutParameter(1, OracleTypes.NUMBER);
				cstmt.setInt(2, foglalasid[i]);
				cstmt.execute();
				eredmenyek[i] = cstmt.getInt(1);
			}
			vissza="A következő jegyek kiadása sikeres:\n";
			int szamlalo=0;
			for(int i=0;i<foglalasid.length;i++){
				if(eredmenyek[i]>0){
					vissza=vissza+eredmenyek[i]+"\n";
					szamlalo++;
				}
			}
			vissza=vissza+".\n";
			if(vissza.equals("A következő jegyek kiadása sikeres:\n.\n"))
				vissza="";
			if(szamlalo!=foglalasid.length){
				vissza=vissza+"A következő jegyek kiadása sikertelen:\n";
				for(int j=0;j<foglalasid.length;j++){
					if(eredmenyek[j]==-1){
						vissza=vissza+foglalasid[j]+": a foglalásazonosító nem létezik! \n";
					}
					else if(eredmenyek[j]==-2){
						vissza=vissza+foglalasid[j]+": a jegyet már kiváltották! \n";
					}
				}
			}

			cstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		}	
		return vissza;
	}
	
	public int AddTerem(Terem t){
		connect();
		try {
			
			cstmt = conn.prepareCall("begin ? := teremadd(?,?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setString(2, t.teremnev);
			cstmt.setString(3, t.teremleiras);
			
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}
	}
	
	public int AddCikk(Cikk c){
		connect();
		try {
			
			cstmt = conn.prepareCall("begin ? := cikkadd(?,?,?,?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setString(2, c.Cikk_cime);
			cstmt.setInt(3, c.FeladoID);
			cstmt.setString(4, c.Bevezeto);
			cstmt.setString(5, c.Szoveg);
			
			
			
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
			System.out.println(eredm);
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}		
	}
	public int AddFelhasznalo(Felhasznalo f){
		connect();
		try {
			
			cstmt = conn.prepareCall("begin ? := felhadd(?,?,?,?,?,?); end;");
			cstmt.registerOutParameter(1, OracleTypes.NUMBER);
			cstmt.setString(2, f.felhnev);
			cstmt.setString(3, f.knev);
			cstmt.setString(4, f.vnev);
			cstmt.setString(5, f.email);
			cstmt.setString(6, f.jelszo);
			cstmt.setInt(7, f.jogosultsag);
			
			cstmt.execute();
			
			int eredm = cstmt.getInt(1);
			
			return eredm;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -10;
			
		}
	}
	public int AddJegytipus(Jegytipus j){
			connect();
			try {
				
				cstmt = conn.prepareCall("begin ? := jegytipusadd(?,?); end;");
				cstmt.registerOutParameter(1, OracleTypes.NUMBER);
				cstmt.setString(2, j.jegytipusnev);
				cstmt.setInt(3, j.kedvezmenymertek);
				
				cstmt.execute();
				
				int eredm = cstmt.getInt(1);
				return eredm;
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return -10;
				
			}
	}
	public int getHanyJegyOsszesen(){
		return 9;
	}
	
	void connect() {
		Properties p = new Properties();
		try {
			p.load( new FileInputStream("db.properties") );
			
			ds = new OracleDataSource();
			ds.setURL(p.getProperty("URL"));
			ds.setUser(p.getProperty("user"));
			ds.setPassword(p.getProperty("pass"));
			conn = ds.getConnection();
			
			//eddig tart a connectelés
			  
			/*
			ResultSet rs = stmt.executeQuery("SELECT deptno, dname from dept");
			while(rs.next()) {
				int deptNo =
				rs.getInt("deptno");
				String deptName =
				rs.getString("dname");
				System.out.println(
				deptNo + " | " + deptName);
			}	
			
			*/
		} catch (FileNotFoundException e) {
			System.out.println("Fájl nem található! connect metodusban (FileNotFoundException)");
			e.printStackTrace();
		} catch (IOException e) {
			System.out.println("I/O hiba. connect metodusban (IOException)");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("SQL hiba connect metodusban (SQLException)");
			e.printStackTrace();
		} catch (Exception e) {
			System.out.println("Egyéb hiba connect metodusban (Exception)");
			e.printStackTrace();
		}
	}

	
	
	
}
